﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace Critr
{
    class logdb
    {
        public static bool Insert(LogSheet ls)
        {
            if (!logdb.AlreadyUploaded(ls))
            {
                using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\logdb.sdf"))
                {
                    conn.Open();

                    SqlCeCommand command = new SqlCeCommand(
                        "INSERT INTO LogHeader(" +
                        "Programmer, BroadcastDate, ShowName, Guest, Time, Description) " +
                        "VALUES(@Programmer, @BroadcastDate, @ShowName, @Guest, @Time, @Description)",
                        conn);

                    command.Parameters.AddWithValue("@Programmer", ls.Programmer);
                    command.Parameters.AddWithValue("@BroadcastDate", ls.BroadcastDate);
                    command.Parameters.AddWithValue("@ShowName", ls.NameOfShow);
                    command.Parameters.AddWithValue("@Guest", ls.Guest);
                    command.Parameters.AddWithValue("@Time", ls.Time);
                    command.Parameters.AddWithValue("@Description", ls.Description);
                    command.ExecuteNonQuery();

                    command = new SqlCeCommand("SELECT @@IDENTITY", conn);
                    Object id = Convert.ToInt32(command.ExecuteScalar());

                    foreach (LogEntry le in ls.Entries)
                    {
                        command = new SqlCeCommand(
                            "INSERT INTO LogEntry(" +
                            "LogHeader_id, Title, Artist, Album, Duration, Log, Cancon, Hit, Chart, Class) " +
                            "VALUES(@id, @Title, @Artist, @Album, @Duration, @Log, @Cancon, @Hit, @Chart, @Class)",
                            conn);

                        command.Parameters.AddWithValue("@id", id);
                        command.Parameters.AddWithValue("@Title", le.Title);
                        command.Parameters.AddWithValue("@Album", le.Album);
                        command.Parameters.AddWithValue("@Artist", le.Artist);
                        command.Parameters.AddWithValue("@Duration", le.Duration);
                        command.Parameters.AddWithValue("@Log", le.Log);
                        command.Parameters.AddWithValue("@Cancon", le.Cancon);
                        command.Parameters.AddWithValue("@Hit", le.Hit);
                        command.Parameters.AddWithValue("@Chart", le.Chart);
                        command.Parameters.AddWithValue("@Class", le.Class);

                        command.ExecuteNonQuery();
                    }
                    conn.Close();

                    return true;
                }
            }
            else
            {
                throw new Exception("This record has already been uploaded to the database.");
            }
        }

        public static bool AlreadyUploaded(LogSheet ls)
        {
            int count;

            using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\logdb.sdf"))
            {
                conn.Open();

                SqlCeCommand command = new SqlCeCommand("SELECT COUNT(*) " +
                    "FROM LogHeader " +
                    "WHERE Programmer = '" + ls.Programmer + "' " +
                    "AND ShowName = '" + ls.NameOfShow + "' " +
                    "AND DATEDIFF(day, BroadcastDate, '" + ls.BroadcastDate.ToString() + "') = 0",
                    conn);

                count = Convert.ToInt32(command.ExecuteScalar());
                conn.Close();
                return (count > 0);
            }
        }
    }
}
